-- **************************************************************************
--    Project Name:   中心出港称重率报表汇总
--    Job Name:       jms_dm.dm_tab_center_weight_rate_net_summary_dt
--    Description :   中心出港称重率报表汇总
--    Author :        申家明
--    date：          2022/08/05
-- **************************************************************************
--    Relation ：
--    jms_dm.dm_tab_center_weight_rate_net_summary_dt << [
--       jms_dm.dm_tab_center_weight_rate_way_detail_needno_dt
--    ]
-- **************************************************************************
insert overwrite table jms_dm.dm_tab_center_weight_rate_net_summary_dt partition (dt)
select site_code
     , site_name
     , proxy_area_code
     , proxy_area_name
     , max(should_weigh_qty1)                                                      as should_weigh_qty1
     , max(no_weigh_qty1)                                                          as no_weigh_qty1
     , round(max(no_weigh_qty1) / nvl(max(should_weigh_qty1), 0), 4)               as qty1_rate
     , max(should_weigh_qty2)                                                      as should_weigh_qty2
     , max(no_weigh_qty2)                                                          as no_weigh_qty2
     , round(max(no_weigh_qty2) / nvl(max(should_weigh_qty2), 0), 4)               as qty1_rate
     , max(less_three_qty)                                                         as less_three_qty
     , round((max(no_weigh_qty1) + max(no_weigh_qty2)) /
             (nvl(max(should_weigh_qty1), 0) + nvl(max(should_weigh_qty2), 0)), 4) as weigh_rate
     , date_time
     , date_time                                                                   as dt
from (
         select date_time
              , site_code
              , site_name
              , proxy_area_code
              , proxy_area_name
              , count(distinct billcode)                     as should_weigh_qty1 --单件应称重票数
--               , sum(if(nvl(weight,0.00) = 0.00  and pjrel_flag = 1 , 1, 0)) as no_weigh_qty1
              , sum(if(nvl(weight,0.00) = 0.00, 1, 0)) as no_weigh_qty1
              , 0                            as should_weigh_qty2
              , 0                            as no_weigh_qty2
              , 0                            as less_three_qty
         from jms_dm.dm_tab_center_weight_rate_way_detail_needno_dt
         where dt = date_add('{{ execution_date | cst_ds }}', -1)
           and flag = 1
         group by date_time
                , site_code
                , site_name
                , proxy_area_code
                , proxy_area_name
         union all
         select date_time
              , site_code
              , site_name
              , proxy_area_code
              , proxy_area_name
              , 0                                                              as should_weigh_qty1 --单件应称重票数
              , 0                                                              as no_weigh_qty1
              , count(distinct billcode)                                                       as should_weigh_qty2
              , sum(case
                        when nvl(package_weight, 0.00 ) > 3 and nvl(weight,0.00) = 0.00  and bagging_site_code <> site_code and jjrel_flag <> 1 and pjrel_flag <> 1
                            then 1
                        else 0 end)                                            as no_weigh_qty2
              , sum(case when nvl(package_weight, 0.00 ) < 3 then 1 else 0 end) as less_three_qty
         from jms_dm.dm_tab_center_weight_rate_way_detail_needno_dt
         where dt = date_add('{{ execution_date | cst_ds }}', -1)
           and flag = 2
          group by date_time
                , site_code
                , site_name
                , proxy_area_code
                , proxy_area_name
     ) a
group by site_code
       , site_name
       , proxy_area_code
       , proxy_area_name
       , date_time

 distribute by dt;
